/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBPackage;

import java.sql.*;

/**
 *
 * @author cloud
 */
public class DBManager {
    
    Connection conn;
    Statement st;
    ResultSet rs;
    
    public void accessDB() {
        String url = "jdbc:derby://localhost:1527/sample";
        String user = "app";
        String pwd = "app";
        try{
            DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
            conn = DriverManager.getConnection(url, user, pwd);
            st = conn.createStatement();
            rs = st.executeQuery("Select * From CUSTOMER");
            while (rs.next()){
                System.out.println("NAME = " + rs.getString("NAME"));
            }
            rs.close();
            st.close();
            conn.close();
        } catch(SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }
    
    public String getNames(){
        String url = "jdbc:derby://localhost:1527/sample";
        String user = "app";
        String pwd = "app";
        String ret = "";
        try{
            DriverManager.registerDriver(new org.apache.derby.jdbc.ClientDriver());
            conn = DriverManager.getConnection(url, user, pwd);
            st = conn.createStatement();
            rs = st.executeQuery("Select * From CUSTOMER");
            while (rs.next()){
                ret += "NAME = " + rs.getString("NAME") + "\n<br/>";
            }
            rs.close();
            st.close();
            conn.close();
        } catch(SQLException ex) {
            System.err.println(ex.getMessage());
        }
        return ret;
    }
    
    private Statement CustomerStatement(){
        String url = "jdbc:derby://localhost:1527/sample";
        String user = "app";
        String pwd = "app";
        Statement stat = null;
        try{
            conn = DriverManager.getConnection(url, user, pwd);
            stat = conn.createStatement();
        } catch (SQLException ex){
            System.err.println(ex.getMessage());
        }
        return stat;
    }
    
    public void InsertCustomer(String name){
        st = CustomerStatement();
        try{
            if(name != null && name.length() > 0){
                st.executeUpdate("insert into customer(customer_id, discount_code, zip, name) values((select max(customer_id)+1 from customer), 'M', '95117', '" + name +"')");
                st.close();
                conn.close();
            }
            else{
                System.out.println("inserisce un parametro");
            }
        } catch (SQLException ex){
            System.err.println(ex.getMessage());
        }
    }
    
    public String getNameLike(String like){
        String ret = "";
        st = CustomerStatement();
        try{
            if(like != null && like.length() > 0){
                rs = st.executeQuery("Select * from customer where name like '%" + like + "%'");
                while (rs.next()){
                    ret += "NAME = " + rs.getString("NAME") + "\n<br/>";
                }
                rs.close();
                st.close();
                conn.close();
            }
            else{
             System.out.println("inserisce un parametro");
            }
        } catch (SQLException ex){
            System.err.println(ex.getMessage());
        }
        return ret;
    }
}
